<?php
/**
 * @author qingyu8@staff.weibo.com
 * @date 2018/04/28
 */
namespace common\logic;

use common\models\Boat;
use common\models\Trip;
use yii\db\Query;
use yii;

class Search {
    public static function search($get) {
        //keyword boatName,路线 国家地区,天数,(等级),设施,特色，旅行时间   排序字段：价格，评价，年代
        $page = Yii::$app->request->get('page', 1);
        $limit = Yii::$app->request->get('limit', 20);
        $planMin = Yii::$app->request->get('planMin');
        $planMax = Yii::$app->request->get('planMax');
        $device = Yii::$app->request->get('device');
        if (!is_numeric($page) || $page < 1 || !is_numeric($limit) || $limit < 1) return ['list'=>[],'total'=>0];
        if (!empty($planMin) && (!is_numeric($planMin) || $planMin < 1)) return ['list'=>[],'total'=>0];
        if (!empty($planMax) && (!is_numeric($planMax) || $planMax < 1)) return ['list'=>[],'total'=>0];
        $offset = ($page - 1) * $limit;
        $orderByKey = !empty($get['orderByKey']) ? $get['orderByKey'] : 'price';
        if (!in_array($orderByKey, [
            'price',
            'rating',
            'year_built'
        ])
        ) return ['list'=>[],'total'=>0];
        $orderBy = isset($get['orderBy']) && $get['orderBy'] == 1 ? 'ASC' : 'DESC';
        if (!empty($get['dateStart']) || !empty($get['dateEnd'])) {
            $q = (new Query())->select('distinct `boat_id`')->from('dive_trip');
            if (!empty($get['dateStart'])) {
                $start = date('Y-m-d H:i:s', strtotime($get['dateStart']));
                $q->andWhere("start_time > '$start'");
            }
            if (!empty($get['dateEnd'])) {
                $end = date('Y-m-d H:i:s', strtotime($get['dateEnd']));
                $q->andWhere("end_time < '$end'");
            }
            $boatIds = $q->orderBy('boat_id')->offset(0)->limit(500)->column();
            if(empty($boatIds)) return ['list'=>[],'total'=>0];
        }
        $q = (new Query())->select('boat_id')->from('dive_search');
        $countQ = (new Query())->select('count(*)')->from('dive_search');
        if (!empty($boatIds)) {
            $boatIds = implode(',', $boatIds);
            $q->andWhere("boat_id IN ($boatIds)");
            $countQ->andWhere("boat_id IN ($boatIds)");
        }
        if (!empty($get['keyword'])) {
            $q->andWhere("(name_en LIKE '%{$get['keyword']}%' OR name_cn LIKE '%{$get['keyword']}%' OR country LIKE '%{$get['keyword']}%' OR country_cn LIKE '%{$get['keyword']}%' OR trips_name LIKE '%{$get['keyword']}%' OR tours_name LIKE '%{$get['keyword']}%')");
            $countQ->andWhere("(name_en LIKE '%{$get['keyword']}%' OR name_cn LIKE '%{$get['keyword']}%' OR country LIKE '%{$get['keyword']}%' OR country_cn LIKE '%{$get['keyword']}%' OR trips_name LIKE '%{$get['keyword']}%' OR tours_name LIKE '%{$get['keyword']}%')");
        }
        if ($planMin) {
            $q->andWhere("days >= $planMin");
            $countQ->andWhere("days >= $planMin");
        }
        if ($planMax) {
            $q->andWhere("days <= $planMax");
            $countQ->andWhere("days <= $planMax");
        }
        if (!empty($get['country'])) {
            $q->andWhere("country LIKE '%{$get['country']}%' OR country_cn LIKE '%{$get['country']}%'");
            $countQ->andWhere("country LIKE '%{$get['country']}%' OR country_cn LIKE '%{$get['country']}%'");
        }
        if ($device) {
            switch ($device) {
                case 'wifi':
                    $q->andWhere("wifi=1");
                    $countQ->andWhere("wifi=1");
                    break;
                case 'nitrox':
                    $q->andWhere("nitrox=1");
                    $countQ->andWhere("nitrox=1");
                    break;
                case 'aircon':
                    $q->andWhere("aircon=1");
                    $countQ->andWhere("aircon=1");
                    break;
                case 'bathroom':
                    $q->andWhere("bathroom=1");
                    $countQ->andWhere("bathroom=1");
                    break;
            }
        }
        //TODO 特色
        $boatIds = $q->orderBy("$orderByKey $orderBy")->offset($offset)->limit($limit)->column();
        $total = $countQ->scalar();
        $res = [];
        if ($boatIds) {
            $boatIdsStr = implode(',', $boatIds);
            $r = Boat::getList("id IN ($boatIdsStr)");
            foreach ($r as $k => $v) {
                $r[$v['id']] = $v;
            }
            foreach ($boatIds as $k => $id) {
                $res[] = $r[$id];
            }
        }
        return [
            'list' => $res,
            'total' => $total,
        ];
    }

    public static function generate() {
        $offset = 0;
        $limit = 20;
        Yii::$app->db->createCommand("DROP TABLE IF EXISTS dive_search_new;CREATE TABLE dive_search_new like dive_search")->execute();
        while (true) {
            $r = Boat::getList('is_valid=1', '*', 'id', $offset, $limit);
            if (empty($r)) break;
            foreach ($r as $item) {
                $sql="SELECT a.name_cn,a.name_en,b.name_cn as tour_cn,b.name_en as tour_en FROM `dive_trip` as a
join dive_tour as b
on a.tour_id = b.id
where a.boat_id={$item['id']}";
                $names = Yii::$app->db->createCommand($sql)->queryAll();
                $trips_name = array_merge(array_unique(array_column($names, 'name_cn')), array_unique(array_column($names, 'name_en')));
                $tours_name = array_merge(array_unique(array_column($names, 'tour_cn')), array_unique(array_column($names, 'tour_en')));
                Yii::$app->db->createCommand()->insert('dive_search_new', [
                    'boat_id' => $item['id'],
                    'name_cn' => $item['name_cn'],
                    'name_en' => $item['name_en'],
                    'country' => $item['countries'],
                    'country_cn' => $item['countries_cn'],
                    //'days' => $item['name_cn'],
                    //'device' => $item['name_cn'],
                    //'start_date' => $item['name_cn'],
                    //'end_date' => $item['name_cn'],
                    //'price' => $item['price'],
                    //'rating' => $item['name_cn'],
                    'year_built' => $item['year_built'],
                    'wifi' => $item['wifi'] === 'No' ? 0 : 1,
                    'nitrox' => $item['nitrox'] === 'No' ? 0 :1,
                    'trips_name' => implode(',', $trips_name),
                    'tours_name' => implode(',', $tours_name),
                ])->execute();
            }
            $offset += $limit;
        }
        self::updateDays();
        self::updatePrice();
        Yii::$app->db->createCommand("DROP TABLE IF EXISTS dive_search_old;RENAME TABLE dive_search TO dive_search_old;RENAME TABLE dive_search_new TO dive_search")->execute();
    }

    private static function updateDays() {
        $offset = 0;
        $limit = 50;
        while (true) {
            $sql = "select boat_id,duration from
(SELECT a.id as tour_id,b.id as trip_id,b.boat_id,a.duration FROM `dive_tour` as a
join dive_trip as b on a.id=b.tour_id) as c order by duration desc limit $offset,$limit";
            $r = Yii::$app->db->createCommand($sql)->queryAll();
            if (empty($r)) break;
            foreach ($r as $item) {
                Yii::$app->db->createCommand()->update('dive_search_new', [
                    'days' => $item['duration']
                ], "boat_id={$item['boat_id']}")->execute();
            }
            $offset += $limit;
        }
    }

    private static function updatePrice() {
        $offset = 0;
        $limit = 50;
        while (true) {
            $sql = "select boat_id,price,bathroom,aircon from
(SELECT distinct b.boat_id,a.cabin_id,a.id,b.bathroom,b.aircon,a.price FROM `dive_occupancy` as a
join dive_cabin as b
on a.cabin_id = b.id
order by a.price desc
    ) as c limit $offset,$limit";
            $r = Yii::$app->db->createCommand($sql)->queryAll();
            if (empty($r)) break;
            foreach ($r as $item) {
                $data = [
                    'price' => $item['price']
                ];
                if ($item['bathroom'] != 0) $data['bathroom'] = $item['bathroom'];
                if ($item['aircon'] != 0) $data['aircon'] = $item['aircon'];
                Yii::$app->db->createCommand()->update('dive_search_new', $data, "boat_id={$item['boat_id']}")->execute();
            }
            $offset += $limit;
        }
    }
}
